Solution: Declare Constraints

The Japanese phrase poka-yoke means “mistake-proofing.” This term refers to a manufacturing process that helps eliminate product defects by preventing, correcting, or drawing attention to errors as they occur. This practice improves quality and decreases the need for correction, which more than makes up for the cost of its use.

We can apply the poka-yoke principle to our database design by using foreign key constraints to enforce referential integrity. Instead of searching for and correcting data integrity mistakes, we can prevent these mistakes from entering our database in the first place.

Our existing code and ad hoc queries must obey the same constraints, so there’s no way for any forgotten code or back doors to bypass enforcement. The database rejects any improper change, no matter where the difference comes from.

Using foreign keys saves us from writing unnecessary code and ensures that our code works the same way even if we change the database. This reduces the time to develop the code and also many hours of debugging and maintenance. The software industry average is 15 to 50 bugs per 1,000 lines of code. All else being equal, if we have fewer lines of code, we have fewer bugs.

Supporting multi-table changes#

Foreign keys have another feature we can’t mimic using application code: cascading updates.

First, let’s use this query and check if the value of the status is updated successfully.

Now, let’s insert BANANA as a value in the status column and update the value to DEFAULT.

The query in the following playground shows the effects on the database.

Now, let’s try to add these queries one by one in the following playground to see their effect on the database.

This solution allows us to update or delete the parent row, enabling the database to take care of any child rows that reference it. Updates to the parent tables BugStatus and Accounts propagate automatically to child rows in Bugs. There’s no longer a catch-22 problem.

The way we declare the ON UPDATE or ON DELETE clause in the foreign key constraint allows us to control the result of a cascading operation. For example, RESTRICT for the foreign key on reported_by means that we can’t delete an account if some rows in Bugs reference it. The constraint blocks the delete and raises an error. Whereas if we delete a status value, any bugs with that status are automatically reset to the default status value.

In either case, the database changes both tables atomically. The foreign key references remain satisfied both before and after the changes.

If we add a new child table to the database, the foreign keys in the child table dictate the cascading behavior. We don’t need to change our application code. Neither do we need to change anything about the parent table, no matter how many child tables reference it.

Overhead? Not really#

It’s true that foreign key constraints have a bit of overhead cost. But compared to the alternative, foreign keys prove to be a lot more efficient.

• We don’t need to run SELECT queries to check before we complete an insert, update, or delete action.
• We don’t need to lock tables to protect multi-table changes.
• We don’t need to run periodic quality control scripts to correct the inevitable orphans.

Foreign keys are easy to use. They improve performance and help us maintain consistent referential integrity during both simple and complex data changes.